# 技术运维下四项数据导出
import datetime

from other.大正.utils.CsvUtils import exportCsvLocalByDataList
from other.大正.utils.DbConnUtil import executeSql


# SQL1:  yyl = 运营率；businessCount = 故障次数
def querySQL1Before201808(startTime, endTime, workTime):
    sql = '''
        SELECT
            sum(busineesCount) as businessCount,
            ROUND((1-sum(IF(a.whdays, a.whdays, 0))/sum(IF(c.count2, c.count2-IFNULL(a.zcdays,0), TIMESTAMPDIFF(day,{_startTime},{_endTime})+1)))*100,2) AS yyl
        FROM
            (
            SELECT
                f_id,
                TIMESTAMPDIFF(DAY,IF (f_work_start_time < {_startTime},{_startTime},f_work_start_time),{_endTime}) + 1 AS count2
            FROM
                t_factory t
            WHERE
            t.f_status = 0
            and f_work_start_time<{_f_work_time}
            ) c
                LEFT JOIN (
            SELECT
                f_factory_id,
                sum(IF (f_work_status IN (1,4,7,8),1,0)) as busineesCount,
                sum(IF (f_work_status IN (1,4,7,8),TIMESTAMPDIFF(HOUR,IF (f_startTime > {_startTime},f_startTime,{_startTime}),IF (f_endTime IS NULL,{_endTime},IF (f_endTime < {_startTime},{_startTime},
                IF (f_endTime > {_endTime},{_endTime},f_endTime)))) / 24,0)) AS whdays,
                sum(IF (f_work_status IN (2,3, 5, 6),TIMESTAMPDIFF(HOUR,IF (f_startTime > {_startTime},f_startTime,{_startTime}),IF (f_endTime IS NULL,{_endTime},IF (f_endTime < {_startTime},{_startTime},
                IF (f_endTime > {_endTime},{_endTime},f_endTime)))) / 24,0)) AS zcdays
            FROM
                t_factory_business
            WHERE
            (
                f_startTime <= {_endTime}
                AND f_endTime >= {_startTime}
            )
            OR (
                f_startTime <= {_endTime}
                AND f_endTime IS NULL
                AND now() >= {_startTime}
            )
            GROUP BY
            f_factory_id
            ) a ON c.f_id = a.f_factory_id
    '''.format(_startTime=repr(startTime),
               _endTime=repr(endTime),
               _f_work_time=repr(workTime))
    result = executeSql(sql)
    return result


def querySQL1(startTime, endTime, workStartTime, workEndTime):
    sql = '''
        SELECT
            sum(busineesCount) as businessCount,
            ROUND((1-sum(IF(a.whdays, a.whdays, 0))/sum(IF(c.count2, c.count2-IFNULL(a.zcdays,0), TIMESTAMPDIFF(day,{_startTime},{_endTime})+1)))*100,2) AS yyl
        FROM
            (
            SELECT
                f_id,
                TIMESTAMPDIFF(DAY,IF (f_work_start_time < {_startTime},{_startTime},f_work_start_time),{_endTime}) + 1 AS count2
            FROM
                t_factory t
            WHERE
            t.f_status = 0
            and f_work_start_time>={_f_work_start_time}
            and f_work_start_time<{_f_work_end_time}
            ) c
                LEFT JOIN (
            SELECT
                f_factory_id,
                sum(IF (f_work_status IN (1,4,7,8),1,0)) as busineesCount,
                sum(IF (f_work_status IN (1,4,7,8),TIMESTAMPDIFF(HOUR,IF (f_startTime > {_startTime},f_startTime,{_startTime}),IF (f_endTime IS NULL,{_endTime},IF (f_endTime < {_startTime},{_startTime},
                IF (f_endTime > {_endTime},{_endTime},f_endTime)))) / 24,0)) AS whdays,
                sum(IF (f_work_status IN (2,3, 5, 6),TIMESTAMPDIFF(HOUR,IF (f_startTime > {_startTime},f_startTime,{_startTime}),IF (f_endTime IS NULL,{_endTime},IF (f_endTime < {_startTime},{_startTime},
                IF (f_endTime > {_endTime},{_endTime},f_endTime)))) / 24,0)) AS zcdays
            FROM
                t_factory_business
            WHERE
            (
                f_startTime <= {_endTime}
                AND f_endTime >= {_startTime}
            )
            OR (
                f_startTime <= {_endTime}
                AND f_endTime IS NULL
                AND now() >= {_startTime}
            )
            GROUP BY
            f_factory_id
            ) a ON c.f_id = a.f_factory_id
    '''.format(_startTime=repr(startTime),
               _endTime=repr(endTime),
               _f_work_start_time=repr(workStartTime),
               _f_work_end_time=repr(workEndTime))
    result = executeSql(sql)
    return result


# SQL2： count = 总数；故障率 = SQL1的businessCount / SQL2的count
def querySQL2Before201808(startTime, endTime, workTime):
    sql = '''
        SELECT
		    count(DISTINCT a.f_order_id) as count ,sum(f_gain_sharing_value_partner+f_gain_sharing_value_station+f_gain_sharing_value_ygl) as money,
		    ROUND(sum(if(t3.f_order_id is null,0,1))/sum(if(a.f_status in (15,50,60),1,0 ))*100,2) as yjl
        FROM
            t_order a
        LEFT  JOIN  t_factory t2 on a.f_factory_id=t2.f_id
        LEFT JOIN t_washcar_alarm t3 ON a.f_order_id = t3.f_order_id
        WHERE
            f_order_date >= {_startTime}
            AND f_order_date < {_endTime}
            and f_work_start_time<{_f_work_time}
            AND (a.f_status IN (50, 60)
                OR (
                    a.f_status = 15 
                    AND a.f_pay_type IN (
                            SELECT
                                f_pay_type
                            FROM
                                t_order_income_config
                            WHERE
                                f_status = 1
                                AND f_is_refund = 0
                            )
                    )
            )
            and f_is_insider=0
    '''.format(_startTime=repr(startTime),
               _endTime=repr(endTime),
               _f_work_time=repr(workTime))
    result = executeSql(sql)
    return result


def querySQL2(startTime, endTime, workStartTime, workEndTime):
    sql = '''
        SELECT
		    count(DISTINCT a.f_order_id) as count ,sum(f_gain_sharing_value_partner+f_gain_sharing_value_station+f_gain_sharing_value_ygl) as money,
		    ROUND(sum(if(t3.f_order_id is null,0,1))/sum(if(a.f_status in (15,50,60),1,0 ))*100,2) as yjl
        FROM
            t_order a
        LEFT  JOIN  t_factory t2 on a.f_factory_id=t2.f_id
        LEFT JOIN t_washcar_alarm t3 ON a.f_order_id = t3.f_order_id
        WHERE
            f_order_date >= {_startTime}
            AND f_order_date < {_endTime}
            and f_work_start_time>={_f_work_start_time}
            and f_work_start_time<{_f_work_end_time}
            AND (a.f_status IN (50, 60)
                OR (
                    a.f_status = 15 
                    AND a.f_pay_type IN (
                            SELECT
                                f_pay_type
                            FROM
                                t_order_income_config
                            WHERE
                                f_status = 1
                                AND f_is_refund = 0
                            )
                    )
            )
            and f_is_insider=0
    '''.format(_startTime=repr(startTime),
               _endTime=repr(endTime),
               _f_work_start_time=repr(workStartTime),
               _f_work_end_time=repr(workEndTime))
    result = executeSql(sql)
    return result


# 声明一些参数集，存储查询的结果
def buildDateList(dataList, result1, result2, dateTime):
    # 设置一个行数据字典参数
    rowData = []
    rowData.append(dateTime)
    rowData.append(result1["yyl"][0])  # 运营率
    rowData.append(result1["businessCount"][0])  # 故障次数
    rowData.append(result1["businessCount"][0] / result2["count"][0] * 100)  # 故障率
    # 放入数据集
    dataList.append(rowData)


# ===========================   开始执行查询逻辑  ================================= #
# 横轴时间范围
startTime = '2022-10-01'
endTime = '2022-11-01'
# 竖轴时间范围
work_start_time = '2018-08-01'
work_end_time = startTime

# 结果集
dataList = []
# 先查一下 2018-08-01之前的数据
result1 = querySQL1Before201808(startTime, endTime, '2018-08-01')
result2 = querySQL2Before201808(startTime, endTime, '2018-08-01')
buildDateList(dataList, result1, result2, '2018-08-01之前')

# 再查一下 2018-08-01之后的数据
while work_start_time != work_end_time:
    reduceOneDay = (datetime.datetime.strptime(work_start_time, '%Y-%m-%d') + datetime.timedelta(days=32)).replace(
        day=1)
    result1 = querySQL1(startTime, endTime, work_start_time, reduceOneDay.strftime("%Y-%m-%d"))
    result2 = querySQL2(startTime, endTime, work_start_time, reduceOneDay.strftime("%Y-%m-%d"))

    buildDateList(dataList, result1, result2, work_start_time)
    work_start_time = reduceOneDay.strftime('%Y-%m-%d')

# 生成本地文件
columnNames = ['竖轴日期', '运营率', '故障次数', '故障率']
exportCsvLocalByDataList(rf"D:\Store\Python-Download\上三项{startTime}.csv", dataList, columnNames)
print('数据导出结束...')
